﻿Imports MySql.Data

Public Class DistrictReports

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim Var As New Variables
        Dim DataTable As New DataTable
        Dim sr As New DataTable

        Try
            Var.Conn.Open()

            Var.Comm = New MySqlClient.MySqlCommand("select * from tbl_Accounts where acc_num = '" & TextBox1.Text & "'", Var.Conn)
            Var.Adapter = New MySqlClient.MySqlDataAdapter(Var.Comm)
            Var.Adapter.Fill(DataTable)

            Label1.Text = DataTable.Rows(0)("acc_name")
            Label2.Text = DataTable.Rows(0)("acc_num")
            Label3.Text = DataTable.Rows(0)("acc_type")

            Var.Comm = New MySqlClient.MySqlCommand("select * from tbl_Accounts where acc_id = '" & DataTable.Rows(0)("acc_id_up") & "'", Var.Conn)
            Var.Adapter = New MySqlClient.MySqlDataAdapter(Var.Comm)
            Dim RegionalManager As New DataTable
            Var.Adapter.Fill(RegionalManager)

            Label4.Text = RegionalManager.Rows(0)("acc_name")

            Var.Comm = New MySqlClient.MySqlCommand("select * from tbl_Accounts where acc_id_up = '" & DataTable.Rows(0)("acc_id") & "'", Var.Conn)
            Var.Adapter = New MySqlClient.MySqlDataAdapter(Var.Comm)
            Var.Adapter.Fill(sr)

            Var.Conn.Close()
        Catch ex As Exception

        End Try

        For row = 0 To sr.Rows.Count - 1
            Dim DataTable2 As New DataTable
            Dim salesman As New DataTable

            Try
                Var.Conn.Open()

                Var.Comm = New MySqlClient.MySqlCommand("SELECT dbo.tbl_Accounts.acc_id, dbo.tbl_Accounts.acc_num, dbo.tbl_Accounts.acc_name, dbo.tbl_Sales.sales_amount, dbo.tbl_Sales.sales_date FROM dbo.tbl_Accounts INNER JOIN dbo.tbl_Sales ON dbo.tbl_Accounts.acc_id = dbo.tbl_Sales.acc_id WHERE dbo.tbl_Accounts.acc_id = '" & sr.Rows(row)("acc_id") & "'", Var.Conn)
                Var.Adapter = New MySqlClient.MySqlDataAdapter(Var.Comm)
                Var.Adapter.Fill(DataTable2)

                Var.Comm = New MySqlClient.MySqlCommand("select * from tbl_Accounts where acc_num = '" & DataTable2.Rows(0)("acc_num") & "'", Var.Conn)
                Var.Adapter = New MySqlClient.MySqlDataAdapter(Var.Comm)
                Var.Adapter.Fill(salesman)


                Var.Conn.Close()
            Catch ex As Exception

            End Try

            Dim total As Integer = 0

            For x = 0 To DataTable2.Rows.Count - 1
                total += DataTable2.Rows(x)("sales_amount")
            Next

            DataGridView1.Rows.Add(salesman.Rows(0)("acc_id"), salesman.Rows(0)("acc_num"), salesman.Rows(0)("acc_name"), total, "")


        Next

        Dim overalltotal As Integer = 0
        If DataGridView1.Rows.Count > 0 Then
            For x = 0 To DataGridView1.Rows.Count - 1
                overalltotal += DataGridView1.Rows(0).Cells("sales_amount").Value
            Next
        End If

        RadLabel7.Text = overalltotal.ToString()
    End Sub

    Private Sub DistrictReports_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    End Sub

    Private Sub RadButton1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RadButton1.Click
        For x = 0 To DataGridView1.Rows.Count - 1
            DataGridView1.Rows.RemoveAt(x)
        Next

        Dim Var As New Variables
        Dim DataTable As New DataTable
        Dim sr As New DataTable

        Try
            Var.Conn.Open()

            Var.Comm = New MySqlClient.MySqlCommand("select * from tbl_Accounts where acc_num = '" & TextBox1.Text & "'", Var.Conn)
            Var.Adapter = New MySqlClient.MySqlDataAdapter(Var.Comm)
            Var.Adapter.Fill(DataTable)

            Var.Comm = New MySqlClient.MySqlCommand("select * from tbl_Accounts where acc_id_up = '" & DataTable.Rows(0)("acc_id") & "'", Var.Conn)
            Var.Adapter = New MySqlClient.MySqlDataAdapter(Var.Comm)
            Var.Adapter.Fill(sr)

            Var.Conn.Close()
        Catch ex As Exception

        End Try



        For row = 0 To sr.Rows.Count - 1
            Dim DataTable2 As New DataTable
            Dim salesman As New DataTable

            Try
                Var.Conn.Open()

                Var.Comm = New MySqlClient.MySqlCommand("SELECT dbo.tbl_Accounts.acc_id, dbo.tbl_Accounts.acc_num, dbo.tbl_Accounts.acc_name, dbo.tbl_Sales.sales_amount, dbo.tbl_Sales.sales_date FROM dbo.tbl_Accounts INNER JOIN dbo.tbl_Sales ON dbo.tbl_Accounts.acc_id = dbo.tbl_Sales.acc_id WHERE dbo.tbl_Accounts.acc_id = '" & sr.Rows(0)("acc_id") & "' and dbo.tbl_Sales.sales_date between '" & RadDateTimePicker1.Value & "' and '" & RadDateTimePicker2.Value & "'", Var.Conn)
                Var.Adapter = New MySqlClient.MySqlDataAdapter(Var.Comm)
                Var.Adapter.Fill(DataTable2)

                Var.Comm = New MySqlClient.MySqlCommand("select * from tbl_Accounts where acc_num = '" & DataTable2.Rows(0)("acc_num") & "'", Var.Conn)
                Var.Adapter = New MySqlClient.MySqlDataAdapter(Var.Comm)
                Var.Adapter.Fill(salesman)


                Var.Conn.Close()
            Catch ex As Exception

            End Try

            Dim total As Integer = 0

            For x = 0 To DataTable2.Rows.Count - 1
                total += DataTable2.Rows(x)("sales_amount")
            Next

            DataGridView1.Rows.Add(salesman.Rows(0)("acc_id"), salesman.Rows(0)("acc_num"), salesman.Rows(0)("acc_name"), total, "")


        Next

        Dim overalltotal As Integer = 0
        If DataGridView1.Rows.Count > 0 Then
            For x = 0 To DataGridView1.Rows.Count - 1
                overalltotal += DataGridView1.Rows(0).Cells("sales_amount").Value
            Next
        End If

        RadLabel7.Text = overalltotal.ToString()
    End Sub
End Class
